from flask import Blueprint, request, render_template, jsonify,redirect,url_for
from flask_login import login_required, current_user
from sqlalchemy import text  # 导入 text 函数
from applications.extensions import db
from applications.models import Settings_web
from applications.schemas import Settings_webOutSchema
from applications.models import software_web
from applications.schemas import software_webOutSchema
from applications.models import kami_web
from applications.schemas import kami_webOutSchema
from applications.common.utils.http import str_to_json
from applications.common.curd import model_to_dicts
from applications.common.helper import ModelFilter
from applications.common.utils.http import fail_api, success_api, table_api
from datetime import datetime

bp = Blueprint('index', __name__, url_prefix='/')


# 首页
@bp.get('/')
@login_required
def indexhome():

    # 暂不开放首页，默认跳转到登录页面
    return render_template('system/index.html', user=current_user)

    # nav_menu = websettings(key_name='Site_Menu',dbName='admin_setings_web')
    nav_menu = Settings_web.query.filter(Settings_web.key_name == 'Site_Menu').first()
    # 数据格式化处理
    nav_menu = Settings_webOutSchema().dump(nav_menu).get('value_name')
    '''
    # nav_menu = Settings_web.query.filter(Settings_web.key_name == 'Site_Menu').first()
    # nav_menu = Settings_webOutSchema().dump(nav_menu).get('value_name')
    # print(nav_menu)
    # <class 'str'>
    [   
      { "name": '首页test', "url": '/index' },
      { "name": '产品', "url": '/product' },
      { 
          "name": '解决方案', 
          "url": 'javascript:;', 
          "children": [
              { "name": '解决方案1', "url": '/solution1' },
              { "name": '解决方案2', "url": '/solution2' },
              { "name": '解决方案3', "url": '/solution3' }
          ]
      },
      { 
          "name": '解决目标', 
          "url": 'javascript:;', 
          "children": [
              { "name": '解决目标1', "url": '/solution1' },
              { "name": '解决目标2', "url": '/solution2' },
              { "name": '解决目标3', "url": '/solution3' },
              { "name": '解决目标4', "url": '/solution4' }
          ]
      },
      { "name": '关于我们', "url": '/about' },
      { "name": '联系我们', "url": '/contact' }
  ]
    '''
    navtitle= '首页'
    try:
        nav_menu = str_to_json(nav_menu)
        return render_template('/www/index.html', user=current_user,nav_menu=nav_menu,navtitle=navtitle)
        
    except Exception as e:
        nav_menu = ['首页']
        return render_template('/www/index.html', user=current_user,nav_menu=nav_menu,navtitle=navtitle)


# 首页
@bp.get('/admin')
@login_required
def index():
    # user = current_user
    
    return render_template('system/index.html', user=current_user)

@bp.get('/about')
def about():
    nav_menu = Settings_web.query.filter(Settings_web.key_name == 'Site_Menu').first()
    nav_menu = Settings_webOutSchema().dump(nav_menu).get('value_name')
    navtitle= '关于'
    return render_template('www/about.html', user=current_user,nav_menu=str_to_json(nav_menu),navtitle=navtitle)


@bp.get('/websettings')
def websettings(key_name='sitename',dbName='admin_setings_web'): # 从数据库中获取数据
    # 查询数据
    try:
        sql = text(f"SELECT * FROM {dbName} WHERE key_name = :key_name")
        result = db.session.execute(sql,{'key_name': key_name})
        data = [dict(row._asdict()) for row in result.fetchall()]
        # 返回json数据
        return jsonify({'code': 200, 'data':data})
    except Exception as e:
        return jsonify({'code': 500, 'message': str(e)})

@bp.get('/website')
def website(dbName='admin_setings_web'): # 从数据库中获取数据
    # 查询数据
    try:
        sql = text(f"SELECT * FROM {dbName}")
        result = db.session.execute(sql)
        data = [dict(row._asdict()) for row in result.fetchall()]
        # 返回json数据
        return jsonify({'code': 200, 'data':data,'msg':'欢迎访问'})
    except Exception as e:
        return jsonify({'code': 500, 'msg': '网站正在调试中...'})
    
@bp.get('/slides')  
def slides(dbName='admin_setings_web'): # 从数据库中获取数据
    # 幻灯片数据格式
    Site_Slides = Settings_web.query.filter(Settings_web.key_name == 'Site_Slides').first()
    # 数据格式化处理
    Site_Slides = Settings_webOutSchema().dump(Site_Slides).get('value_name')
    
    if Site_Slides:
        Site_Slides = str_to_json(Site_Slides)
    else:
        Site_Slides = [
            {
                "id": 1,
                "title": "Slide 1",
                "image": "https://via.placeholder.com/800x400?text=Slide+1",
                "description": "This is the description for slide 1."
            },
            {
                "id": 2,
                "title": "Slide 2",
                "image": "https://via.placeholder.com/800x400?text=Slide+2",
                "description": "This is the description for slide 2."
            },
            {
                "id": 3,
                "title": "Slide 3",
                "image": "https://via.placeholder.com/800x400?text=Slide+3",
                "description": "This is the description for slide 3."
            
            }
        ]
        Site_Slides = str_to_json(Site_Slides)
    return jsonify({'code': 200, 'data':Site_Slides,'msg':'成功'})

@bp.route('/software' ,methods=['GET','POST'])
def software(dbName='admin_software'): # 从数据库中获取数据
    # 查询数据
    try:
        filename = request.args.get('filename')
        # 如果filename为空,则默认为站距计算器DBSPTools
        if not filename:
            filename = '测试软件'
        sql = text(f"SELECT * FROM {dbName} WHERE filename = :filename")
        result = db.session.execute(sql,{'filename': filename})
        data = [dict(row._asdict()) for row in result.fetchall()]
        # 返回json数据
        return jsonify({'code': 200, 'data':data[0],'msg':'成功'})
    except Exception as e:
        pass
        return jsonify({'code': 500, 'msg': '网站正在调试中...'})

@bp.route('/software/download' ,methods=['GET','POST'])
def software_download(): # 从数据库中获取数据
    # 查询数据
    try:
        # GET协议
        filename = request.args.get('filename')
        count = request.args.get('count')
        # 转为整数型
        count = int(count)+1
        # 更新数据库
        sql = text(f"UPDATE admin_software SET count = :count WHERE filename = :filename")
        db.session.execute(sql,{'count': count,'filename': filename})
        db.session.commit()

        # 查询数据
        sql = text(f"SELECT `url`,`count` FROM admin_software WHERE filename = :filename")
        result = db.session.execute(sql,{'filename': filename})
        data = [dict(row._asdict()) for row in result.fetchall()]
        # 返回json数据
        return jsonify({'code': 200, 'data':data[0],'msg':'成功'})
    except Exception as e:
        pass
        return jsonify({'code': 500, 'msg': f'网站正在调试中...\n{str(e)}'})


def get_card_status(card, password):
    sql = text("SELECT * FROM admin_kami WHERE card = :card AND password = :password")
    result = db.session.execute(sql, {'card': card, 'password': password})
    data = [dict(row._asdict()) for row in result.fetchall()]
    return data[0] if data else None

def get_uid_status(uid):
    sql = text("SELECT * FROM admin_kami WHERE uid = :uid")
    result = db.session.execute(sql, {'uid': uid})
    data = [dict(row._asdict()) for row in result.fetchall()]
    # 更新data中的password字段内容替换为*
    for i in range(len(data)):
        data[i]['password'] = data[i]['password'][:4] + '*' * (len(data[i]['password'])-8) + data[i]['password'][-4:]
    return data if data else []

def update_card_status(card, password, status, uid=None):
    if uid:
        sql = text("UPDATE admin_kami SET status = :status, uid = :uid WHERE card = :card AND password = :password")
        db.session.execute(sql, {'status': status, 'uid': uid, 'card': card, 'password': password})
    else:
        sql = text("UPDATE admin_kami SET status = :status WHERE card = :card AND password = :password")
        db.session.execute(sql, {'status': status, 'card': card, 'password': password})
    db.session.commit()

def deactivate_old_cards(uid):
    sql = text("UPDATE admin_kami SET status = '已过期' WHERE uid = :uid")
    db.session.execute(sql, {'uid': uid})
    db.session.commit()

# api/v1/auth接口
@bp.route('/api/v1/auth', methods=['GET', 'POST'])
def auth():
    projectname = request.args.get('projectname') # 通过GET请求获取projectname参数
    # 判断projectname请求方式get或者post
    if projectname == None: # 如果projectname为空，则通过POST请求获取projectname参数
        projectname = request.form.get('projectname') # 通过POST请求获取projectname参数
    if projectname == 'kami': # 卡密验证
        card = request.args.get('card')
        password = request.args.get('password')
        uid = request.args.get('uid')

        if not uid:
            return jsonify({'code': 400, 'msg': '请求错误, 客户端uid。'})

        # 查询数据库根据UID
        uid_info_list = get_uid_status(uid)
        if uid_info_list:
            valid_card_info = None
            for uid_info in uid_info_list:
                status = uid_info['status']
                expire_time_str = uid_info['expire_time']
                try:
                    expire_time = datetime.strptime(expire_time_str, '%Y-%m-%d %H:%M:%S.%f')
                except ValueError:
                    expire_time = datetime.strptime(expire_time_str, '%Y-%m-%d %H:%M:%S')

                if status == '生效中' and expire_time > datetime.now():
                    valid_card_info = uid_info
                    break
                elif status in ['未使用', '已过期','停用']:
                    if expire_time <= datetime.now() and status == '未使用':
                        return jsonify({'code': 200, 'msg': '卡密已过期，请联系管理员续费。'})
                    # elif status == '停用':
                    #     return jsonify({'code': 400, 'msg': '卡密已停用，请联系管理员。'})
                    else: 
                        valid_card_info = uid_info

            if valid_card_info:
                return jsonify({'code': 200, 'data': valid_card_info, 'msg': '成功'})
            else:
                # 处理卡密状态更新逻辑
                deactivate_old_cards(uid)

        # 查询数据库根据卡号和密码
        card_info = get_card_status(card, password)
        if card_info:
            status = card_info['status']
            expire_time_str = card_info['expire_time']
            try:
                expire_time = datetime.strptime(expire_time_str, '%Y-%m-%d %H:%M:%S.%f')
            except ValueError:
                expire_time = datetime.strptime(expire_time_str, '%Y-%m-%d %H:%M:%S')

            if status == '生效中' and expire_time > datetime.now():
                # update_card_status(card, password, '生效中', uid)
                return jsonify({'code': 400, 'msg': '卡密已被使用，请更换卡密。'})
            elif status in ['未使用', '已过期']:
                if expire_time <= datetime.now() and status == '未使用':
                    return jsonify({'code': 200, 'msg': '卡密已过期，请联系管理员续费。'})
                update_card_status(card, password, '生效中', uid)
                return jsonify({'code': 200, 'data': card_info, 'msg': '成功'})
            elif status == '停用':
                return jsonify({'code': 400, 'msg': '卡密已停用，请联系管理员。'})
            elif status == '已过期':
                return jsonify({'code': 400, 'msg': '卡密已过期，请联系管理员续费。'})
        else:
            return jsonify({'code': 400, 'msg': '请求错误, 请购买卡密。'})
    elif projectname == 'test': # 测试
        return jsonify({'code': 200, 'msg': '成功'})
    else:
        return jsonify({'code': 400, 'msg': '请求无效。'})





